package com.yict.common.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.yict.common.bean.ExcelBean;
import com.yict.common.entity.PageEntity;

public class ExcelUtil {

	public static void createExcel(ExcelBean excelBean, OutputStream outputStream) throws Exception {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet(excelBean.getSheetName());

		Row row = sheet.createRow(0);

		String[] head = (String[]) excelBean.getSelfieldsName()[0];
		for (int i = 0; i < head.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(head[i]);
		}

		for (int i = 0; i < excelBean.getDataList().size(); i++) {
			row = sheet.createRow(i + 1);
			Object obj = excelBean.getDataList().get(i);

			for (int j = 0; j < excelBean.getSelfields().length; j++) {
				String name = excelBean.getSelfields()[j];
				Cell cell = row.createCell(j);
				cell.setCellValue(BeanUtils.getSimpleProperty(obj, name));
			}
		}

		wb.write(outputStream);
	}

	public static void createExcelByMap(ExcelBean excelBean, OutputStream outputStream) throws Exception {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet(excelBean.getSheetName());

		Row row = sheet.createRow(0);

		String[] head = (String[]) excelBean.getSelfieldsName()[0];
		for (int i = 0; i < head.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(head[i]);
		}

		for (int i = 0; i < excelBean.getDataList().size(); i++) {
			row = sheet.createRow(i + 1);
			@SuppressWarnings("unchecked")
			Map<String, Object> map = (Map<String, Object>) excelBean.getDataList().get(i);

			for (int j = 0; j < excelBean.getSelfields().length; j++) {
				String key = excelBean.getSelfields()[j];
				Cell cell = row.createCell(j);
				cell.setCellValue(map.get(key).toString());
			}
		}

		wb.write(outputStream);
	}

	/**
	 * 利用poi读取Excel文件
	 * 
	 * @param filePath
	 *            excel文件路径
	 * @throws IOException
	 */
	public static List<Object> readExcel(String filePath, List<Object> list) throws IOException {
		FileInputStream fis = new FileInputStream(filePath); // 根据excel文件路径创建文件流
		POIFSFileSystem fs = new POIFSFileSystem(fis); // 利用poi读取excel文件流
		HSSFWorkbook wb = new HSSFWorkbook(fs); // 读取excel工作簿
		HSSFSheet sheet = wb.getSheetAt(0); // 读取excel的sheet，0表示读取第一个、1表示第二个.....

		// 获取sheet中总共有多少行数据sheet.getPhysicalNumberOfRows()
		for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
			HSSFRow row = sheet.getRow(i); // 取出sheet中的某一行数据
			if (row != null) {
				// 获取该行中总共有多少列数据row.getLastCellNum()
				for (int j = 0; j < row.getLastCellNum(); j++) {
					HSSFCell cell = row.getCell(j); // 获取该行中的一个单元格对象

					// 判断单元格数据类型，这个地方值得注意：当取某一行中的数据的时候，需要判断数据类型，否则会报错
					// java.lang.NumberFormatException: You cannot get a string
					// value from a numeric cell等等错误
					if (cell.getCellType() == 0) {
						System.out.print(Math.round(cell.getNumericCellValue())); // 一般的数据类型在excel中读出来都为float型
					} else {
						System.out.print(cell.getStringCellValue());
					}
				}
				System.out.println("\t");
			}
		}
		return null;
	}

	/**
	 * 拷贝行并填充数据
	 * 
	 * @param wb
	 *            workbook
	 * @param pSourceSheetName
	 *            源表单名称
	 * @param pTargetSheetName
	 *            目标表单名称
	 * @param pStartRow
	 *            起始读取行
	 * @param pEndRow
	 *            结束读取行
	 * @param pPosition
	 *            目标保存
	 */
	public static void copyRows(HSSFWorkbook wb, String pSourceSheetName, String pTargetSheetName, int pStartRow, int pEndRow, int pPosition) {
		HSSFRow sourceRow = null;
		HSSFRow targetRow = null;
		HSSFCell sourceCell = null;
		HSSFCell targetCell = null;
		HSSFSheet sourceSheet = null;
		HSSFSheet targetSheet = null;
		int cType;
		int i;
		int j;
		int targetRowFrom;
		int targetRowTo;

		if ((pStartRow == -1) || (pEndRow == -1)) {
			return;
		}
		sourceSheet = wb.getSheet(pSourceSheetName);
		targetSheet = wb.getSheet(pTargetSheetName);

		List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
		for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
			oldRanges.add(sourceSheet.getMergedRegion(i));
		}

		// 拷贝合并的单元格
		for (int k = 0; k < oldRanges.size(); k++) {
			CellRangeAddress oldRange = oldRanges.get(k);
			CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(), oldRange.getFirstColumn(),
					oldRange.getLastColumn());

			if (oldRange.getFirstRow() >= pStartRow && oldRange.getLastRow() <= pEndRow) {
				targetRowFrom = oldRange.getFirstRow() - pStartRow + pPosition;
				targetRowTo = oldRange.getLastRow() - pStartRow + pPosition;
				oldRange.setFirstRow(targetRowFrom);
				oldRange.setLastRow(targetRowTo);
				targetSheet.addMergedRegion(oldRange);
				sourceSheet.addMergedRegion(newRange);
			}
		}
		// 设置列宽
		for (i = pStartRow; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow != null) {
				for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {
					targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
					targetSheet.setColumnHidden(j, false);
				}
				break;
			}
		}
		// 拷贝行并填充数据
		for (; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow == null) {
				continue;
			}
			targetRow = targetSheet.createRow(i - pStartRow + pPosition);
			targetRow.setHeight(sourceRow.getHeight());
			for (j = sourceRow.getFirstCellNum(); j < sourceRow.getPhysicalNumberOfCells(); j++) {
				sourceCell = sourceRow.getCell(j);
				if (sourceCell == null) {
					continue;
				}
				targetCell = targetRow.createCell(j);
				// targetCell.setEncoding(((Object) sourceCell).getEncoding());
				targetCell.setCellStyle(sourceCell.getCellStyle());
				cType = sourceCell.getCellType();
				targetCell.setCellType(cType);
				switch (cType) {
					case HSSFCell.CELL_TYPE_BOOLEAN :
						targetCell.setCellValue(sourceCell.getBooleanCellValue());
						// System.out.println("--------TYPE_BOOLEAN:" +
						// targetCell.getBooleanCellValue());
						break;
					case HSSFCell.CELL_TYPE_ERROR :
						targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
						// System.out.println("--------TYPE_ERROR:" +
						// targetCell.getErrorCellValue());
						break;
					case HSSFCell.CELL_TYPE_FORMULA :
						// parseFormula这个函数的用途在后面说明
						targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
						// System.out.println("--------TYPE_FORMULA:" +
						// targetCell.getCellFormula());
						break;
					case HSSFCell.CELL_TYPE_NUMERIC :
						targetCell.setCellValue(sourceCell.getNumericCellValue());
						// System.out.println("--------TYPE_NUMERIC:" +
						// targetCell.getNumericCellValue());
						break;
					case HSSFCell.CELL_TYPE_STRING :
						targetCell.setCellValue(sourceCell.getRichStringCellValue());
						// System.out.println("--------TYPE_STRING:" + i +
						// targetCell.getRichStringCellValue());
						break;
				}

			}

		}

	}

	private static String parseFormula(String pPOIFormula) {
		final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
		StringBuffer result = null;
		int index;

		result = new StringBuffer();
		index = pPOIFormula.indexOf(cstReplaceString);
		if (index >= 0) {
			result.append(pPOIFormula.substring(0, index));
			result.append(pPOIFormula.substring(index + cstReplaceString.length()));
		} else {
			result.append(pPOIFormula);
		}

		return result.toString();
	}

	public static HSSFCell getCell(HSSFRow row, int columnId) {
		if (row == null || columnId < 0) {
			return null;
		}

		HSSFCell cell = row.getCell(columnId);
		if (cell == null) {
			cell = row.createCell(columnId);
		}

		return cell;
	}

	public static HSSFCell getCell(HSSFSheet sheet, int rowId, int columnId) {
		if (sheet == null || rowId < 0 || columnId < 0) {
			return null;
		}

		HSSFRow row = sheet.getRow(rowId);
		if (row == null) {
			row = sheet.createRow(rowId);
		}

		HSSFCell cell = row.getCell(columnId);
		if (cell == null) {
			cell = row.createCell(columnId);
		}

		return cell;
	}

	public static void setCellValue(HSSFCell cell, Object value) {
		if (cell == null)
			return;
		if (value == null) {
			cell.setCellValue("");
		} else if (value instanceof String) {
			cell.setCellValue((String) value);
		} else if (value instanceof Integer) {
			cell.setCellValue((Integer) value);
		} else if (value instanceof Double) {
			cell.setCellValue((Double) value);
		} else if (value instanceof Date) {
			cell.setCellValue(DateUtil.DateToString((Date) value, 2));
		}
	}

	public static HSSFRow getRow(HSSFSheet sheet, int rowId) {
		if (sheet == null || rowId < 0) {
			return null;
		}

		HSSFRow row = sheet.getRow(rowId);
		if (row == null) {
			row = sheet.createRow(rowId);
		}

		return row;
	}

	public static HSSFSheet getSheet(HSSFWorkbook workbook, int sheetId) {
		if (workbook == null || sheetId < 0) {
			return null;
		}

		HSSFSheet sheet = workbook.getSheetAt(sheetId);
		if (sheet == null) {
			sheet = workbook.createSheet("sheet" + sheetId);
		}

		return sheet;
	}

	/**
	 * 重新设置单元格计算公式
	 * 
	 * @param wb
	 */
	public static void resetCellFormula(HSSFWorkbook wb) {
		int sheetNum = wb.getNumberOfSheets();

		for (int i = 0; i < sheetNum; i++) {
			HSSFSheet sheet = wb.getSheetAt(i);

			int rows = sheet.getLastRowNum();
			for (int j = 0; j < rows; j++) {
				HSSFRow row = sheet.getRow(j);

				if (row == null)
					continue;

				int cols = row.getLastCellNum();
				for (int k = 0; k < cols; k++) {
					HSSFCell cell = row.getCell(k);

					if (cell == null)
						continue;

					if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
						cell.setCellFormula(cell.getCellFormula());
					}
				}

			}
		}
	}

	public static void main(String[] args) throws Exception {
		try {
			String ruleFilePath = "d:\\Example.xls";

			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(ruleFilePath));
			HSSFWorkbook wb = new HSSFWorkbook(fs);

			// source ,target 为,源sheet 页和目标sheet页,
			// copyRows(wb, "sheet1", "sheet1", 1, 10, 30);

			int beginRowIndex = 4;
			HSSFSheet sheet = wb.getSheetAt(0);
			// sheet.shiftRows(20, sheet.getLastRowNum(), 10);

			int cellType = 0;
			Object value = null;
			String temp = null;
			for (int i = beginRowIndex; i < beginRowIndex + 577; i++) {
				for (int j = 0; j < 9; j++) {

					HSSFCell cell = ExcelUtil.getCell(sheet, i, j);
					cellType = cell.getCellType();
					switch (cellType) {
						case HSSFCell.CELL_TYPE_BOOLEAN :
							value = cell.getBooleanCellValue();
							break;
						case HSSFCell.CELL_TYPE_ERROR :
							break;
						case HSSFCell.CELL_TYPE_FORMULA :
							break;
						case HSSFCell.CELL_TYPE_NUMERIC :
							value = cell.getDateCellValue();
							cell.setCellValue(DateUtil.DateToString((Date) value, 8));
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							break;
						case HSSFCell.CELL_TYPE_STRING :
							value = cell.getStringCellValue();
							temp = value.toString();
							if (temp.indexOf(":") < 0) {

//							} else if (temp.indexOf(":") == 1) {
//								cell.setCellValue("0" + temp);
							}
							break;
					}
				}
			}

			String exportFile = "exlsample" + System.currentTimeMillis() + ".xls";
			FileOutputStream fileOut = new FileOutputStream("d:\\" + exportFile);
			wb.write(fileOut);
			fileOut.flush();
			fileOut.close();
			System.out.println("Operation finished");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
}
